﻿using MicroDb.Helper;

using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;


namespace MicroDb
{

    public abstract class SqlExecute
    {
        private readonly ConcurrentDictionary<string, DbTransaction> _cache = new ConcurrentDictionary<string, DbTransaction>();

        //  private readonly static Pools<DbConnection> pools = new Pools<DbConnection>(30, 3);

        private string GetThreadKey() => Thread.CurrentThread.ManagedThreadId.ToString();


        #region PrepareCommand

        /// <summary>
        /// Command预处理
        /// </summary>
        /// <param name="conn">MySqlConnection对象</param>
        /// <param name="trans">MySqlTransaction对象，可为null</param>
        /// <param name="cmd">MySqlCommand对象</param>
        /// <param name="cmdType">CommandType，存储过程或命令行</param>
        /// <param name="cmdText">SQL语句或存储过程名</param>
        /// <param name="cmdParms">MySqlCommand参数数组，可为null</param>
        private void PrepareCommand(DbConnection conn, DbTransaction trans, DbCommand cmd, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
            }
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
        }

        #endregion


        /// <summary>
        /// 创建一个新的链接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        protected abstract DbConnection CreateDbConnection();


        protected abstract DbParameter[] CreateParameters(IDictionary<string, object> parameters);

        /// <summary>
        /// sql日志
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        public virtual void Logs(string commandText, IDictionary<string, object> parameters)
        {

        }

        /// <summary>
        /// 插入过滤器
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public virtual bool InsertFilter(string propertyName, ref object value)
        {
            return true;
        }

        /// <summary>
        /// 自定义方法
        /// </summary>
        /// <returns></returns>
        public virtual string CustomMethod(string methodName, Expression obj, IReadOnlyList<Expression> paras, Func<Expression, string> changeValue)
        {
            return null;
        }

        /// <summary>
        /// 更新过滤器
        /// </summary>
        /// <param name="propertyName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public virtual bool UpdateFilter(string propertyName, ref object value)
        {
            return true;
        }

        public virtual void UpdateColumnsFilter(Dictionary<string, object> columns, Type tableType)
        {

        }

        private DbParameter[] ParseParameters(IDictionary<string, object> parameters)
        {
            if (parameters == null || parameters.Count == 0) return null;
            return CreateParameters(parameters);
        }



        /// <summary>
        /// 执行非查询
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, IDictionary<string, object> parameters)
        {
            Logs(commandText, parameters);

            if (_cache.TryGetValue(GetThreadKey(), out DbTransaction trans))
            {
                using DbCommand command = trans.Connection.CreateCommand();
                PrepareCommand(trans.Connection, trans, command, commandType, commandText, ParseParameters(parameters));
                return command.ExecuteNonQuery();
            }
            using var connection = CreateDbConnection();
            using DbCommand cmd = connection.CreateCommand();
            PrepareCommand(connection, trans, cmd, commandType, commandText, ParseParameters(parameters));
            return cmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 执行单结果
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType commandType, string commandText, IDictionary<string, object> parameters)
        {
            Logs(commandText, parameters);

            if (_cache.TryGetValue(GetThreadKey(), out DbTransaction trans))
            {
                using DbCommand command = trans.Connection.CreateCommand();
                PrepareCommand(trans.Connection, trans, command, commandType, commandText, ParseParameters(parameters));
                return command.ExecuteScalar();
            }
            using var connection = CreateDbConnection();
            using DbCommand cmd = connection.CreateCommand();
            PrepareCommand(connection, null, cmd, commandType, commandText, ParseParameters(parameters));
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 执行查询返回reader
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteList<T>(CommandType commandType, string commandText, IDictionary<string, object> parameters)
        {
            Logs(commandText, parameters);
            var connect = CreateDbConnection();
            using DbCommand cmd = connect.CreateCommand();
            PrepareCommand(connect, null, cmd, commandType, commandText, ParseParameters(parameters));
            using var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return DBReaderHelper.ToList<T>(reader);
        }
        public T ExecuteEntity<T>(CommandType commandType, string commandText, IDictionary<string, object> parameters)
        {
            return ExecuteList<T>(commandType, commandText, parameters).FirstOrDefault();
        }

        internal object ExecuteEntityObject(CommandType commandType, string commandText, IDictionary<string, object> parameters, Type type)
        {
            Logs(commandText, parameters);
            var connect = CreateDbConnection();
            using DbCommand cmd = connect.CreateCommand();
            PrepareCommand(connect, null, cmd, commandType, commandText, ParseParameters(parameters));
            using var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            var list = DBReaderHelper.ToArrayList(reader, type);
            if (list.Count > 0)
                return list[0];
            return null;
        }

        internal object ExecuteListObject(CommandType commandType, string commandText, IDictionary<string, object> parameters, Type type)
        {
            Logs(commandText, parameters);
            var connect = CreateDbConnection();
            using DbCommand cmd = connect.CreateCommand();
            PrepareCommand(connect, null, cmd, commandType, commandText, ParseParameters(parameters));
            using var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            var list = DBReaderHelper.ToArrayList(reader, type);
            return list.ToArray(type);
        }



        /// <summary>
        /// 当前线程执行事物 限定了当前线程
        /// </summary>
        /// <param name="action"></param>
        public Task DoTran(Action action)
        {
            return Task.Run(() =>
            {
                var threadId = GetThreadKey();
                DbConnection connection = CreateDbConnection();
                connection.Open();
                DbTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                _cache.TryAdd(threadId, trans);
                try
                {
                    action();
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    _cache.TryRemove(threadId, out _);
                    connection.Close();
                }
            });
        }

        /// <summary>
        /// 当前线程执行事物
        /// </summary>
        /// <param name="action"></param>
        public Task<T> DoTran<T>(Func<T> action)
        {
            return Task.Factory.StartNew<T>(() =>
            {
                var threadId = GetThreadKey();
                DbConnection connection = CreateDbConnection();
                connection.Open();
                DbTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    _cache.TryAdd(threadId, trans);
                    T result = action();
                    trans.Commit();
                    return result;
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    connection.Close();
                    _cache.TryRemove(threadId, out _);
                }
            });
        }

    }
}
